using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Smo = Microsoft.SqlServer.Management.Smo;
using System.Xml;

using SqlWebAdmin.CSharp;

public partial class CreateEditTable : System.Web.UI.Page
{
    protected System.Text.StringBuilder sb = new System.Text.StringBuilder();

    protected void Page_Init(object sender, EventArgs e)
    {
        btnSave.Click += new EventHandler(btnSave_Click);
    }

    void btnSave_Click(object sender, EventArgs e)
    {
        if (txtTableXml != null && !String.IsNullOrEmpty(txtTableXml.Value))
        {
            XmlDocument xml = new XmlDocument();
            xml.LoadXml(txtTableXml.Value);

            XmlNode tableDef = xml.DocumentElement;

            // get the name of the database
            string databaseName = Request.QueryString[QueryStringKeys.Database];
            
            // create a new instance of the SMO server class
            Smo.Server smoServer = SmoHelper.GetServer();
            try
            {
                // get the SMO database
                Smo.Database smoDatabase = smoServer.Databases[databaseName];
                if (smoDatabase != null)
                {
                    Smo.Table newTable = new Microsoft.SqlServer.Management.Smo.Table(smoDatabase, tableDef.Attributes["name"].Value, tableDef.Attributes["schema"].Value);

                    foreach (XmlElement columnDef in tableDef.ChildNodes)
                    {
						if (columnDef.Attributes["name"].Value.Length > 0)
						{
							Smo.Column column = new Smo.Column(newTable, columnDef.Attributes["name"].Value);
							column.DataType = GetDataType(columnDef); // (Smo.DataType)Enum.Parse(typeof(Smo.DataType), columnDef.Attributes["dataTypeName"].Value);

							//column.DataType = Smo.DataType.Int;//.sSmo.DataType.(Smo.DataType)Enum.Parse(typeof(Smo.DataType), columnDef.Attributes["dataTypeName"].Value);
							column.Identity = Boolean.Parse(columnDef.Attributes["identity"].Value);
							column.IdentityIncrement = Int32.Parse(columnDef.Attributes["identityIncrement"].Value);
							column.IdentitySeed = Int32.Parse(columnDef.Attributes["identitySeed"].Value);
							column.Nullable = Boolean.Parse(columnDef.Attributes["nullable"].Value);
							//                column.InPrimaryKey         = Boolean.Parse(columnDef.Attributes["key"].Value);
							column.RowGuidCol = Boolean.Parse(columnDef.Attributes["isRowGuid"].Value);

							newTable.Columns.Add(column);
						}
                    }

                    if (newTable.Columns.Count > 0)
                    {
                        newTable.Create();

                        Response.Redirect( SiteUrls.DatabaseProperties( Request.QueryString[QueryStringKeys.Database] ));
                    }
                }
            }
            catch( Exception ex )
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
			}
			finally
			{
				smoServer.ConnectionContext.Disconnect();
			}
        }
    }

	private Smo.DataType GetDataType(XmlElement column)
	{
		Smo.DataType retDataType = null;

		int dataSize = Int32.Parse(column.Attributes["size"].Value);
		int dataScale = Int32.Parse(column.Attributes["scale"].Value);
		int dataPrecision = Int32.Parse(column.Attributes["precision"].Value);
		string dataUdtName = column.Attributes["udtName"].Value;
		string dataUdtSchema = column.Attributes["udtSchema"].Value;

		switch ( column.Attributes["dataTypeName"].Value.ToLower())
		{
			case "bigint": //Smo.DataType.BigInt
				retDataType = Smo.DataType.BigInt;
				break;
			case "binary": //Smo.DataType.Binary
				retDataType = Smo.DataType.Binary(dataSize);
				break;
			case "bit": //Smo.DataType.Bit
				retDataType = Smo.DataType.Bit; 
				break;
			case "char": //Smo.DataType.Char
				retDataType = Smo.DataType.Char(dataSize);
				break;
			case "datetime": //Smo.DataType.DateTime
				retDataType = Smo.DataType.DateTime;
				break;
			case "decimal": //Smo.DataType.Decimal
				retDataType = Smo.DataType.Decimal(dataScale,dataPrecision);
				break;
			case "float": //Smo.DataType.Float
				retDataType = Smo.DataType.Float;
				break;
			case "image": //Smo.DataType.Image
				retDataType = Smo.DataType.Image;
				break;
			case "int": //Smo.DataType.Int
				retDataType = Smo.DataType.Int;
				break;
			case "money": //Smo.DataType.Money
				retDataType = Smo.DataType.Money;
				break;
			case "nchar": //Smo.DataType.NChar
				retDataType = Smo.DataType.NChar(dataSize);
				break;
			case "ntext": //Smo.DataType.NText
				retDataType = Smo.DataType.NText;
				break;
			case "nvarchar": //Smo.DataType.NVarChar
				retDataType = Smo.DataType.NVarChar(dataSize);
				break;
			case "nvarcharmax": //Smo.DataType.NVarCharMax
				retDataType = Smo.DataType.NVarCharMax;
				break;
			case "real": //Smo.DataType.Real
				retDataType = Smo.DataType.Real;
				break;
			case "smalldatetime": //Smo.DataType.SmallDateTime
				retDataType = Smo.DataType.SmallDateTime;
				break;
			case "smallint": //Smo.DataType.SmallInt
				retDataType = Smo.DataType.SmallInt;
				break;
			case "smallmoney": //Smo.DataType.SmallMoney
				retDataType = Smo.DataType.SmallMoney;
				break;
			case "sysname": //Smo.DataType.SysName
				retDataType = Smo.DataType.SysName;
				break;
			case "text": //Smo.DataType.Text
				retDataType = Smo.DataType.Text;
				break;
			case "timestamp": //Smo.DataType.Timestamp
				retDataType = Smo.DataType.Timestamp;
				break;
			case "tinyint": //Smo.DataType.TinyInt
				retDataType = Smo.DataType.TinyInt;
				break;
			case "uniqueidentifier": //Smo.DataType.UniqueIdentifier
				retDataType = Smo.DataType.UniqueIdentifier;
				break;
			case "userdefineddatatype": //Smo.DataType.UserDefinedDataType
				retDataType = Smo.DataType.UserDefinedDataType(dataUdtName, dataUdtSchema);
				break;
			case "userdefinedtype": //Smo.DataType.UserDefinedType
				retDataType = Smo.DataType.UserDefinedType(dataUdtName, dataUdtSchema);
				break;
			case "varbinary": //Smo.DataType.VarBinary
				retDataType = Smo.DataType.VarBinary(dataSize);
				break;
			case "varbinarymax": //Smo.DataType.VarBinaryMax
				retDataType = Smo.DataType.VarBinaryMax;
				break;
			case "varchar": //Smo.DataType.VarChar
				retDataType = Smo.DataType.VarChar(dataSize);
				break;
			case "varcharmax": //Smo.DataType.VarCharMax
				retDataType = Smo.DataType.VarCharMax;
				break;
			case "variant": //Smo.DataType.Variant
				retDataType = Smo.DataType.Variant;
				break;
			case "xml": //Smo.DataType.Xml
				retDataType = Smo.DataType.Xml(dataUdtName, dataUdtSchema);
				break;
		}
		return retDataType;

	}
    protected void Page_Load(object sender, EventArgs e)
    {
//        this.RegisterStartupScript("CreateTableLoad", "<script language=\"javascript\" type=\"text/javascript\">LoadData(0)</script>");
        // if not a postback
        if (!Page.IsPostBack)
        {
            lblError.Visible = false;

           
                lblError.Visible = false;

                // get the name of the database
                string databaseName = Request.QueryString[QueryStringKeys.Database];
                
                // create a new instance of the SMO server class
				Smo.Server smoServer = SmoHelper.GetServer();
                try
                {
                    

                    // get the SMO database
                    Smo.Database smoDatabase = smoServer.Databases[databaseName];
                    if (smoDatabase != null)
                    {
                        string tableName;
                        string schemaName;
                        // get the SMO Tables collection
                        smoServer.SetDefaultInitFields(typeof(Smo.Table), "IsSystemObject"); // for performance 
                        Smo.TableCollection smoTables = smoDatabase.Tables;
                        string[] columnStrings = null;

                        if (String.IsNullOrEmpty(Request.QueryString[QueryStringKeys.Schema]) ||
                            String.IsNullOrEmpty(Request.QueryString[QueryStringKeys.Table]))
                        {
                            // the user will usually use the default schema
                            schemaName = smoDatabase.DefaultSchema;


                            // set the initial default table name and check that it doesn't exists
                            int i = 0;
                            do
                            {
                                i += 1;
                                tableName = "Table" + i.ToString();
                            } while (smoTables.Contains(tableName));

                            // just create the dummy rows since the table doesn't exists
                            columnStrings = new string[] {"","","","","","","","","",""};
                            for (int c = 0; c < 10; c++)
                            {
                                columnStrings[c] = "new Column( \"\" , \"int\", 4, true, false, false, 0, 0, null, 0, 0, false, \"\", \"\")";
                            }
                        }
                        else
                        {
                            tableName = Request.QueryString[QueryStringKeys.Table];
                            schemaName = Request.QueryString[QueryStringKeys.Schema];

                            if (!smoTables.Contains(tableName))
                                Response.Redirect(SiteUrls.Tables(databaseName));
                            else
                            {
                                Smo.Table table = smoTables[tableName, schemaName];
                                int count = table.Columns.Count;
                                columnStrings = new string[count];

                                for( count = 0; count < table.Columns.Count; count++ ) 
                                {
                                    Smo.Column col = table.Columns[count];
                                    bool userDefined = col.DataType.SqlDataType.ToString() == "UserDefinedDataType" ? true : false;
                                    columnStrings[count] = string.Format("new Column( \"{0}\" , \"{1}\", {2}, {3}, {4}, {5}, {6}, {7}, \"{8}\", {9}, {10}, {11}, \"{12}\", \"{13}\" )",
                                        col.Name, col.DataType.SqlDataType.ToString().ToLower(), col.DataType.MaximumLength, col.Nullable.ToString().ToLower(), col.InPrimaryKey.ToString().ToLower(), col.Identity.ToString().ToLower(), col.Identity ? col.IdentitySeed : 0, col.Identity ? col.IdentityIncrement : 0, col.Default, col.DataType.NumericPrecision, col.DataType.NumericScale, col.RowGuidCol.ToString().ToLower(), userDefined ? col.DataType.Name : String.Empty, userDefined ? col.DataType.Schema : String.Empty);
                                }
                            }
                        }
    
                        sb.Append("var __tbcounter = -1;" + Environment.NewLine);
                        sb.AppendFormat("var tableDef = new Table(\"{0}\",\"{1}\");" + Environment.NewLine, tableName, schemaName );
                        foreach (string s in columnStrings)
                        {
                            sb.AppendFormat("tableDef.columns[++__tbcounter] = {0};" + Environment.NewLine, s );
                        }
                        txtTableName.Text = tableName;
                        txtSchemaName.Text = schemaName;
                    }
                    else
                    {
                        Response.Redirect(SiteUrls.Error(Globals.databaseNotFoundErrorNumber), false);
                    }
                }
                catch (Smo.SmoException smoEx)
                {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(smoEx));
                    lblError.Visible = true;
                }
                catch (Exception ex)
                {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(ex));
                    lblError.Visible = true;
				}
				finally
				{
					smoServer.ConnectionContext.Disconnect();
				}
            }

        
    }

    protected override void OnPreRender(EventArgs e)
    {
        base.OnPreRender(e);
		ClientScript.RegisterStartupScript(this.GetType(), this.ClientID + "ControlNameDeclaration", String.Format("var tableNameTextBoxControlID = '{0}';{1}", txtTableName.ClientID,Environment.NewLine), true);
//        ClientScript.RegisterClientScriptBlock(this.GetType(), this.ClientID + "Script", sb.ToString(), true);
        ClientScript.RegisterStartupScript(this.GetType(), this.ClientID + "StartupScript", sb.ToString() + Environment.NewLine + "LoadData(0);", true);
    }

}
